package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.canin.model.Toothbrush;
import com.canin.util.CaninUtil;

public class ToothbrushDAO
{
    private Connection conn = null;

    private Map<String, String> sizes = CaninUtil.initializeSizes();
	private Map<String, String> types = CaninUtil.initializeToothbrushType();

    public ToothbrushDAO(Connection conn)
    {
        this.conn = conn;
    }

    public ArrayList<Toothbrush> getAllToothbrush() throws Exception
    {
        ArrayList<Toothbrush> toothbrushList = new ArrayList<Toothbrush>();

        String sql = "select * from toothbrush";
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Toothbrush toothbrush = new Toothbrush();
            toothbrush.setId( rs.getInt( "id" ) );
            toothbrush.setName( rs.getString( "name" ) );
            toothbrush.setBrand( rs.getString( "brand" ) );
            toothbrush.setDescription( rs.getString( "description" ) );
            toothbrush.setSize( rs.getString( "size" ) );
            toothbrush.setType( rs.getString( "type" ) );
            toothbrush.setLength( rs.getDouble( "length" ) );
            toothbrush.setShippingPrice( rs.getInt( "shippingPrice" ) );
            toothbrush.setPrice( rs.getInt( "price" ) );
            
            if ( toothbrush.getSize() != null )
            {
            	toothbrush.setThaiSize( sizes.get( toothbrush.getSize() ) );
            }
            toothbrush.setThaiType( types.get( toothbrush.getType() ) );
            
            toothbrushList.add( toothbrush );
        }

        rs.close();
        ps.close();

        return toothbrushList;
    }
	
	public TreeMap<String, String> getToothbrushBrand() throws Exception
	{
		TreeMap<String, String> toothbrushBrandList = new TreeMap<String, String>();
		
		String sql = "select distinct(brand) from toothbrush where brand!=''";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			toothbrushBrandList.put( rs.getString( "brand" ), rs.getString( "brand" ) );
		}

		rs.close();
		ps.close();
		
		return toothbrushBrandList;
	}
	
	public void addNewToothbrush( Toothbrush toothbrush ) throws Exception
	{
		String sql = "insert into toothbrush(name, brand, description, size, price, shippingPrice, type, length)" +
				" value(?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, toothbrush.getName());
		ps.setString(2, toothbrush.getBrand());
		ps.setString(3, toothbrush.getDescription());
		ps.setString(4, toothbrush.getSize());
		ps.setInt(5, toothbrush.getPrice());
		ps.setInt(6, toothbrush.getShippingPrice());
		ps.setString(7, toothbrush.getType());
		ps.setDouble(8, toothbrush.getLength());
		
		ps.executeUpdate();
		ps.close();
	}

    public ArrayList<Toothbrush> getToothbrushBySize( String size ) throws Exception
    {
        ArrayList<Toothbrush> toothbrushList = new ArrayList<Toothbrush>();

        String sql = "select * from toothbrush where size=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, size);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Toothbrush toothbrush = new Toothbrush();
            toothbrush.setId( rs.getInt( "id" ) );
            toothbrush.setName( rs.getString( "name" ) );
            toothbrush.setBrand( rs.getString( "brand" ) );
            toothbrush.setDescription( rs.getString( "description" ) );
            toothbrush.setSize( rs.getString( "size" ) );
            toothbrush.setType( rs.getString( "type" ) );
            toothbrush.setLength( rs.getDouble( "length" ) );
            toothbrush.setShippingPrice( rs.getInt( "shippingPrice" ) );
            toothbrush.setPrice( rs.getInt( "price" ) );
            toothbrush.setImageName( rs.getString( "imageName" ) );
            
            if ( toothbrush.getSize() != null )
            {
            	toothbrush.setThaiSize( sizes.get( toothbrush.getSize() ) );
            }
            toothbrush.setThaiType( types.get( toothbrush.getType() ) );
            
            toothbrushList.add( toothbrush );
        }

        rs.close();
        ps.close();

        return toothbrushList;
    }
    

    
}
